package com.logistica.metropolitana.db.daos;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.transform.ResultTransformer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.logistica.metropolitana.db.model.AeConstatarSitio;
import com.logistica.metropolitana.db.model.AeEquInstalado;
import com.logistica.metropolitana.db.model.AeImagene;
import com.logistica.metropolitana.db.model.AeObservacione;
import com.logistica.metropolitana.db.model.AeSitio;
import com.logistica.metropolitana.db.model.AeVisita;
import com.logistica.metropolitana.exceptions.ActaDeEntregaException;

/**
 * 
 * @author rioslore
 *
 */
@Repository
public class ActaDeEntregaDAO extends HibernateDaoSupport{

	private final Logger logger = LoggerFactory.getLogger(ActaDeEntregaDAO.class);

	@Autowired
	public ActaDeEntregaDAO(SessionFactory sessionFactory){
		setSessionFactory(sessionFactory);
	}

	public AeSitio getSitio(Long id) throws ActaDeEntregaException{
		try{
			return getHibernateTemplate().get(AeSitio.class, id);
		}catch(Exception e){
			logger.error("Hubo un error al tratar de obtener un Sitio");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_SELECT);
			throw aException;
		}
	}

	public int existeSitio(Long id) throws ActaDeEntregaException{
		Long count;
		try{
			count = (Long) getSession()
					.createQuery("SELECT count(a) FROM AeSitio a WHERE a.gid = :gid")
						.setParameter("gid", id) .uniqueResult();
		}catch(Exception e){
			logger.error("Hubo un error al tratar de obtener un Sitio");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_SELECT);
			throw aException;
		}
		return count.intValue();
	}

	@SuppressWarnings("unchecked")
	public List<AeVisita> getVisita(Long id) throws ActaDeEntregaException{
		List<AeVisita> visitas = Lists.newArrayList();
		try{
			String sql =  "SELECT * FROM ae_visitas WHERE gid ="+id; 
			Query query = getSession().createSQLQuery(sql).setResultTransformer(new AeVisitaTransformer());
			visitas = query.list();
			return visitas;
		}catch(Exception e){
			logger.error("Hubo un error al obtener las visitas");
			ActaDeEntregaException aException = new ActaDeEntregaException(e,
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_SELECT);
			throw aException;
		}
	}

	@SuppressWarnings("unchecked")
	public List<AeSitio> getActaEntregaList() throws ActaDeEntregaException{
		List<AeSitio> actas = Lists.newArrayList();
		try{
			String sql = "SELECT GID, NOMBRE_SITIO, TIPO_SITIO, ESTATUS, INSTITUCION, FECHA FROM AE_SITIO";
			Query query = getSession().createSQLQuery(sql).setResultTransformer(new AcSitioTransformer());
			actas = query.list();
		}catch(Exception e){
			logger.error("Hubo un error al tratar de obtener la lista de Actas");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_SELECT);
			throw aException;
		}
		return actas;
	}

	@SuppressWarnings("unchecked")
	public List<AeSitio> searchActaByGid(String gid) throws ActaDeEntregaException {
		List<AeSitio> actas = Lists.newArrayList();
		try{
			String sql = "SELECT GID, NOMBRE_SITIO, TIPO_SITIO, ELABORO, INSTITUCION, FECHA FROM ae_sitio WHERE gid="+gid+";";
			Query query = getSession().createSQLQuery(sql).setResultTransformer(new AcSitioTransformer());
			actas = query.list();
		}catch(Exception e){
			logger.error("Hubo un error al tratar de buscar la Acta");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_SELECT);
			throw aException;
		}
		return actas;
	}

	private class AcSitioTransformer implements ResultTransformer{

		private static final long serialVersionUID = -1056290863583101416L;

		@Override
		public Object transformTuple(Object[] tuple, String[] aliases){
			AeSitio sitio = new AeSitio();
			sitio.setGid(((BigInteger) tuple[0]).longValue());
			sitio.setNombreSitio((String) tuple[1]);
			sitio.setTipoSitio((String) tuple[2]);
			sitio.setEstatus((String) tuple[3]);
			sitio.setInstitucion((String) tuple[4]);
			sitio.setFecha((Date) tuple[5]);
			return sitio;
		}

		@SuppressWarnings("rawtypes")
		@Override
		public List transformList(List collection){
			return collection;
		}
	}

	private class AeVisitaTransformer implements ResultTransformer{

		private static final long serialVersionUID = -1056290863583101416L;

		@Override
		public Object transformTuple(Object[] tuple, String[] aliases){
			AeVisita visita = new AeVisita();
			visita.setIdVisita(((BigInteger) tuple[0]).longValue());
			visita.setNumVisita(((Short) tuple[2]).intValue());
			visita.setFolioVisita(((BigInteger) tuple[3]).longValue());
			visita.setObservacion((String) tuple[4]);
			visita.setFecha((Date) tuple[5]);
			return visita;
		}

		@SuppressWarnings("rawtypes")
		@Override
		public List transformList(List collection){
			return collection;
		}
	}

	public void addActaDeEntrega(AeSitio aeSitio) throws ActaDeEntregaException{
		Transaction transaction = getSession().getTransaction();
		try{
			transaction.begin();
			getHibernateTemplate().persist(aeSitio.getAePrueba());
			getHibernateTemplate().persist(aeSitio.getAePaiInterno());
			getHibernateTemplate().persist(aeSitio.getAePaiExterno());
			
			AeConstatarSitio aeConstatarSitio = aeSitio.getAeConstatarSitio();
			getHibernateTemplate().persist(aeConstatarSitio);
			if(!aeConstatarSitio.getAeObservaciones().isEmpty()){
				for(AeObservacione obs : aeConstatarSitio.getAeObservaciones()){
					if(obs != null){
						obs.setAeConstatarSitio(aeConstatarSitio);
						getHibernateTemplate().persist(obs);
					}
				}
			}

			getHibernateTemplate().persist(aeSitio);

			List<AeImagene> imagenes = aeSitio.getAeImagenes();
			if(!imagenes.isEmpty()){
				for(AeImagene img : imagenes){
					if(img != null){
						img.setAeSitio(aeSitio);
						getHibernateTemplate().persist(img);
					}
				}
			}
			List<AeEquInstalado> equipos = aeSitio.getAeEquInstalados();
			if(!equipos.isEmpty()){
				for(AeEquInstalado eq : equipos){
					eq.setAeSitio(aeSitio);
					getHibernateTemplate().persist(eq);
				}
			}
			transaction.commit();
		}catch(Exception e){
			transaction.rollback();
			logger.error("Hubo un error al tratar de agregar una Acta de Entrega");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_INSERT);
			throw aException;
		}
	}

	public void addVisita(AeVisita visita) throws ActaDeEntregaException{
		Transaction transaction = getSession().getTransaction();
		try{
			transaction.begin();
			getHibernateTemplate().persist(visita);
			transaction.commit();
		}catch(Exception e){
			transaction.rollback();
			logger.error("Hubo un error al tratar de agregar Visita al Acta de Entrega");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_INSERT);
			throw aException;
		}
	}

	public void editActaDeEntrega(AeSitio aeSitio) throws ActaDeEntregaException{
		Transaction transaction = getSession().getTransaction();
		try{
			transaction.begin();

			if(aeSitio.getAeConstatarSitio() != null){
				getSession().createQuery("DELETE FROM AeObservacione a WHERE a.aeConstatarSitio.idConstatar = :constatarId")
								.setParameter("constatarId", aeSitio.getAeConstatarSitio().getIdConstatar()).executeUpdate();
				if(!aeSitio.getAeConstatarSitio().getAeObservaciones().isEmpty()){
					if(aeSitio.getAeConstatarSitio().getAeObservaciones()!=null){
						for (AeObservacione observacion : aeSitio.getAeConstatarSitio().getAeObservaciones()) {
							observacion.setAeConstatarSitio(aeSitio.getAeConstatarSitio());
							getHibernateTemplate().persist(observacion);
						}
					}
				}
			}

			List<AeEquInstalado> equInstalados = aeSitio.getAeEquInstalados();
			if(equInstalados != null){
				for (AeEquInstalado ei : equInstalados) {
					if(ei.getIdEquinst()!=null){
						getHibernateTemplate().update(ei);
					}
					else{
						getHibernateTemplate().persist(ei);
					}
				}
			}

			getHibernateTemplate().update(aeSitio.getAeConstatarSitio());
			getHibernateTemplate().update(aeSitio.getAePrueba());
			getHibernateTemplate().update(aeSitio.getAePaiInterno());
			getHibernateTemplate().update(aeSitio.getAePaiExterno());
			
			List<AeImagene> imagenes = aeSitio.getAeImagenes();
			if(!imagenes.isEmpty()){
				getSession().createQuery("DELETE FROM AeImagene a WHERE a.aeSitio.gid = :gid")
							.setParameter("gid", aeSitio.getGid()).executeUpdate();
				for(AeImagene img : imagenes){
					if(img != null){
						img.setAeSitio(aeSitio);
						getHibernateTemplate().persist(img);
					}
				}
			}
			getHibernateTemplate().update(aeSitio);
			transaction.commit();
		}catch(Exception e){
			transaction.rollback();
			logger.error("Hubo un error al tratar de editar un Acta de Entrega");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_UPDATE);
			throw aException;
		}
	}

	public void deleteActaDeEntrega(AeSitio aeSitio) throws ActaDeEntregaException {
		Transaction transaction = getSession().getTransaction();
		try{
			transaction.begin();
			List<AeObservacione> observaciones = aeSitio.getAeConstatarSitio().getAeObservaciones();
			getHibernateTemplate().deleteAll(observaciones);
			getHibernateTemplate().deleteAll(aeSitio.getAeVisitas());
			getHibernateTemplate().deleteAll(aeSitio.getAeEquInstalados());
			getHibernateTemplate().deleteAll(aeSitio.getAeImagenes());
			getHibernateTemplate().delete(aeSitio);
			getHibernateTemplate().delete(aeSitio.getAeConstatarSitio());
			getHibernateTemplate().delete(aeSitio.getAePrueba());
			getHibernateTemplate().delete(aeSitio.getAePaiInterno());
			getHibernateTemplate().delete(aeSitio.getAePaiExterno());
			transaction.commit();
		}catch(Exception e){
			transaction.rollback();
			logger.error("Hubo un error al tratar de eliminar una Acta de Entrega");
			ActaDeEntregaException aException = new ActaDeEntregaException(e, 
					ActaDeEntregaException.LAYER_DAO, ActaDeEntregaException.ACTION_DELETE);
			throw aException;
		}
	}
}
